#!/usr/bin/env python
#encoding: utf-8

import os, sys, time
from subprocess import Popen, PIPE
import shlex
from optparse import OptionParser
DIRNAME = os.path.dirname(__file__)
OPSTOOLS = os.path.abspath(os.path.join(DIRNAME, '..'))
sys.path.append(OPSTOOLS)
from library.mysql import MySQLDConfig
import MySQLdb

# MySQL实例的数据目录(会以name为子目录)
MYSQL_DATA_DIR = '/var/mysqlmanager/data'
# MySQL实例的配置文件目录
MYSQL_CONF_DIR = '/var/mysqlmanager/conf'
# MySQL实例的备份文件目录
MYSQL_BACK_DIR = '/var/mysqlmanager/back'

# 主从复制master的用户和密码
REPLICATION_USER = 'repl'
REPLICATION_PASS = '123456'

# 设置选项参数
def opt():
    parser = OptionParser()
    parser.add_option(
        '-n', '--name',      # 指定实例的name
        dest    = 'name',
        action  = 'store',
        default = 'myinstance'
    )
    parser.add_option(
        '-p', '--port',      # 指定端口
        dest    = 'port',
        action  = 'store',
        default = '3306'
    )
    parser.add_option(
        '-c', '--command',   # create check and so on...
        dest    = 'command',
        action  = 'store',
        default = 'check'
    )
    options, args = parser.parse_args()
    return options, args

# 初始化目录(如果不存在则创建)
def _init():
    if not os.path.exists(MYSQL_DATA_DIR):
        os.makedirs(MYSQL_DATA_DIR)
    if not os.path.exists(MYSQL_CONF_DIR):
        os.makedirs(MYSQL_CONF_DIR)

# 使用glob模块读取配置文件，返回配置文件列表(绝对路径)
def readConfs():
    import glob
    confs = glob.glob(MYSQL_CONF_DIR + '/*.cnf')
    return confs

# 检查指定的配置文件中端口号是否与指定端口相同
def checkPort(conf_file, port):
    mc = MySQLDConfig(conf_file)
    if mc.mysqld_vars['port'] == port:
        return True
    else:
        return False

# 有些配置项的值由具体示例而定，将这些不定项设置为字典
def _genDict(name, port):
    return {
        'pid-file': os.path.join(MYSQL_DATA_DIR, name,'%s.pid' % name),
        'socket': '/tmp/%s.sock' % name,
        'port': port,
        'datadir': os.path.join(MYSQL_DATA_DIR, name),
        'log-error': os.path.join(MYSQL_DATA_DIR, name, '%s.log' % name),
    }

# 通过配置文件名称->配置文件的绝对路径
def getCNF(name):
    cnf = os.path.join(MYSQL_CONF_DIR, '%s.cnf' % name)
    return cnf

# MySQL执行安装过程
def mysql_install(name):
    cnf = getCNF(name)
    cmd = 'mysql_install_db --defaults-file=%s' % cnf
    p = Popen(shlex.split(cmd), stdout=PIPE, stderr=PIPE)
    p.communicate()
    p.returncode

# 为保险起见，设置MySQL实例数据目录的属主数组
def setOwner(datadir):
    os.system('chown mysql:mysql %s -R' % datadir)

# 运行MySQL实例
def mysql_run(name):
    cnf = getCNF(name)
    cmd = 'mysqld_safe --defaults-file=%s &' % cnf
    p = Popen(cmd, stdout=PIPE, stderr=PIPE, shell=True)
    time.sleep(2)
    p.returncode

# 创建MySQL实例主函数, 由create子命令触发调用
def createInstance(name, port, **kw):
    exists_confs = readConfs()

    # 检查给定的端口号和名称是否已经使用
    for conf in exists_confs:
        if conf.split('/')[-1][:-4] == name:
            print >> sys.stderr, 'Instance: %s is exists' % name
            sys.exit(-1)
        if checkPort(conf, port):
            print >> sys.stderr, 'Port: %s is exists' % port
            sys.exit(-1)

    # 1. 生成配置文件
    cnf = getCNF(name)
    if not os.path.exists(cnf):
        c = _genDict(name, port)
        c.update(kw)
        mc = MySQLDConfig(cnf, **c)
        mc.save()
    datadir = os.path.join(MYSQL_DATA_DIR, name)
    if not os.path.exists(datadir):
        # 2. 安装初始化MySQL实例
        mysql_install(name)
        # 3. 设置属主属组
        setOwner(datadir)
        # 4. 启动MySQL运行
        mysql_run(name)
        
# 根据配置文件信息连接MySQL数据库
def connMysql(name):
    cnf = getCNF(name)
    if os.path.exists(cnf):
        mc = MySQLDConfig(cnf)
        port = int(mc.mysqld_vars['port'])
        host = '127.0.0.1'
        user = 'root'
        conn = MySQLdb.connect(host=host , port=port, user=user,)
        cur = conn.cursor()
        return cur

# 获取内存中全局变量, 输出是一个字典.
def getMyVariables(cur):
    sql = 'show global variables'
    cur.execute(sql)
    data = cur.fetchall()
    return dict(data)

# 对比配置文件和内存中变量, 找出不一致相
# 遗留问题: 单位不一致和目录最后'/'符号问题 ?????????????????????????????????
def diffMyVariables(name):
    cur = connMysql(name)
    vars = getMyVariables(cur)
    cnf = getCNF(name)
    mc = MySQLDConfig(cnf)
    for k, v in mc.mysqld_vars.items():
        k = k.replace('-', '_')
        if k in vars and v != vars[k]:
            print k, v, vars[k]

# 根据命令行参数调整配置文件
def setMyVariables(name, k, v):
    cnf = getCNF(name)
    mc = MySQLDConfig(cnf)
    mc.set_vars(k, v)
    mc.save()

# 设置主从用户时执行创建用户的SQL语句
def runSQL(name):
    sql = "grant replication slave on *.* to %s@'%%' identified by '%s'" % (REPLICATION_USER, REPLICATION_PASS)
    cur = connMysql(name)
    cur.execute(sql)

# 代替配置文件，在slave上设置master相关信息, 注意最后2个缺省参数(是为restore命令而加)
def changeMaster(name, host, port, user, password, logfile=None, logpos=None):
    if logfile is None:
        sql = """change master to 
                 master_host = '%s',
                 master_port = %s,
                 master_user = '%s',
                 master_password = '%s'
              """ % (host, port, user, password)
    else:
        sql = """change master to 
                 master_host = '%s',
                 master_port = %s,
                 master_user = '%s',
                 master_password = '%s',
                 master_log_file = '%s',
                 master_log_pos = %s
              """ % (host, port, user, password, logfile, logpos)

    cur = connMysql(name)
    cur.execute(sql)

# MySQL备份，有待完善，调整下灵活性
def backupMysql(name):
    import datetime
    now = datetime.datetime.now()
    ts = now.strftime('%Y-%m-%d.%H:%M:%S')
    sqlfile = os.path.join(MYSQL_BACK_DIR, name, '%s.sql' % ts)
    _dir = os.path.dirname(sqlfile)

    # 如果目录不存在，则创建
    if not os.path.exists(_dir):
        os.makedirs(_dir)
    cnf = getCNF(name)
    mc = MySQLDConfig(cnf)
    port = mc.mysqld_vars['port']
    # -A：所有数据库    -x：dump时锁表，只读   -F：dump前刷log
    cmd = "mysqldump -A -x -F --master-data=1 --host=127.0.0.1 --port=%s --user=root > %s 2>/dev/null" % (port, sqlfile)
    p = Popen(cmd, stdout=PIPE, shell=True)
    stdin, stdout = p.communicate()
    p.returncode

# 定义MySQL备份恢复主函数，参数需要指定备份的sql文件 
def restoreMysql(name, port, sqlfile, **kw):
    # 创建从实例 传入选项参数
    createInstance(name, port, **kw)
    cnf = getCNF(name)
    mc = MySQLDConfig(cnf)
    port = mc.mysqld_vars['port']
    # 执行导入sql的shell命令
    cmd = "mysql -h 127.0.0.1 -u root -P %s < %s" % (port, sqlfile)
    p = Popen(cmd, stdout=PIPE, shell=True)
    stdin, stdout = p.communicate()
    p.returncode

# 定义内部正则过滤参数的功能函数, 获取：master-log-file 和 master-log-pos
def findPos(s):
    import re
    rlog = re.compile(r"MASTER_LOG_FILE='(\S+)'")
    rpos = re.compile(r"MASTER_LOG_POS=(\d+)")
    log = rlog.search(s)
    pos = rpos.search(s)
    if log and pos:
        return log.group(1), pos.group(1)
    else:
        return (None, None)

# 读SQL文件，返回log、pos
def getPos(sqlfile):
    with open(sqlfile) as fd:
        for line in fd:
            log, pos = findPos(line)
            if log and pos:
                return log, pos

if __name__ == '__main__':
    _init()
    options, args = opt()
    instance_name = options.name
    instance_port = options.port
    instance_cmd  = options.command
    if instance_cmd == 'create': 
        if not args:
            createInstance(instance_name, instance_port)
        else:
            dbtype = args[0]
            serverid = args[1]
            mysql_options = {'server-id': serverid}
            if dbtype == 'master':
                mysql_options['log-bin'] = 'mysql-bin'
                createInstance(instance_name, instance_port, **mysql_options) 
                runSQL(instance_name)
            elif dbtype == 'slave':
                # 5.5 版本以上不建议将master开头字段写入配置文件，其实低版本写入配置文件也是不妥当的
                #mysql_options['master-host'] = args[2]
                #mysql_options['master-port'] = args[3]
                #mysql_options['master-user'] = REPLICATION_USER
                #mysql_options['master-pass'] = REPLICATION_PASS
                mysql_options['replicate-ignore-db'] = 'mysql'    # 设置忽略mysql库
                mysql_options['skip-slave-start'] = None          # 设置slave运行时不自动启动主从复制
                createInstance(instance_name, instance_port, **mysql_options)  # 按照从选项来创建实例
                host = args[2]    # 传入master的主机
                port = args[3]    # 传入master的端口
                user = REPLICATION_USER
                password = REPLICATION_PASS
                # 设置master信息
                changeMaster(instance_name, host, port, user, password) 
    elif instance_cmd == 'check':
        diffMyVariables(instance_name)
    elif instance_cmd == 'adjust':
        try:
            k = args[0]
        except IndexError:
            print 'Usage: -c adjust max_connections 300. At least 1 arg.'
        try:
            v = args[1]
        except IndexError:
            v = None
        setMyVariables(instance_name, k, v)
    elif instance_cmd == 'backup':     # MySQL备份命令
        backupMysql(instance_name)
    elif instance_cmd == 'restore':    # MySQL备份的恢复命令
        server_id = args[0]
        master_host = args[1]
        master_port = args[2]
        sqlfile = args[3]
        mysqld_options = {'server-id': server_id}
        mysqld_options['skip-slave-start'] = None
        mysqld_options['replicate-ignore-db'] = 'mysql'
        # 恢复固定SQL内容(SQL文件备份的内容)
        restoreMysql(instance_name, instance_port, sqlfile, **mysqld_options)
        logfile, logpos = getPos(sqlfile)
        # 指定log和pos，来配置slave激活master的主从复制(实现增量备份的恢复)
        changeMaster(instance_name, master_host, master_port, REPLICATION_USER, REPLICATION_PASS, logfile, logpos)



# 遗留待完善功能如下：
# 1. 完善mysql工具箱，-c 支持shutdown、start、restart、show(查看有哪些实例，端口号分别是多少)
# 2. 美化check的输出，值一样的不输出
# 3. adjust的时候，如果mysql不支持这个配置项要有提示信息
# 4. 执行-c的时候判断后面的命令是否存在，如果不存在要有提示(列表列举，看是否存在)

